Java Database Programming with JDBC Java Database Programming with JDBC
by Pratik Patel
Coriolis, The Coriolis Group
ISBN: 1576100561   Pub Date: 10/01/96
  

Previous Table of Contents Next


Listing 7.5 Dynamically generating a pie chart from a database query.

/*
Example 7-2: Pie chart
*/
import java.awt.*;
import java.applet.Applet;
import java.sql.*;
import java.util.StringTokenizer;

public class example72 extends java.applet.Applet {
   String url;
  String Name;
  Connection con;
  TextArea OutputField = new TextArea(10,35);
  NFPiechartApp pie;

public void init() {
  setLayout(new BorderLayout());
  url="jdbc:msql://elanor/jdbctest";
  pie = new NFPiechartApp(this);

  ConnectToDB();

  add("North", OutputField);
  String columnData[] = getData("select * from Cost");

  ShowFormattedData(columnData);
  ShowChartData(columnData[1],columnData[0]);
  add("Center", pie);

}

public void ConnectToDB() {


  try {
    new imaginary.sql.iMsqlDriver();
    con = DriverManager.getConnection(url, "prpatel", "");
  }
  catch( Exception e ) {
    e.printStackTrace();
    System.out.println(e.getMessage());
  }

}

public void ShowFormattedData(String[] columnD ) {

int i;

for ( i=0; i< columnD.length; i++) {
  OutputField.appendText(columnD[i]+"\n");
  }

}

public void ShowChartData(String dataNumber, String dataLabel) {

StringTokenizer nData, lData;
String SliceData = "";
ColorGenerator colorGen = new ColorGenerator();

// We need to assign colors to the pie slices automatically, so we use a
// class that cycles through colors. See this class defined below.

nData = new StringTokenizer(dataNumber, ",");
lData = new StringTokenizer(dataLabel, ",");
// We used our preformatted column data, and need to break it down to the
// elements. We use the StringTokenizer to break the column string data
// individual down by commas we inserted when we created the data.

// We assume that dataNumber and dataLabel have the same number of
// elements since we just generated them from the getData method.

while(nData.hasMoreTokens()) {
// Loop through the dataNumber and dataLabel and build the slice data:
// ( 1234, darkBlue, "Label" ). This is what the pie chart class expects,
// so we must parse our data and put it in this format.

SliceData += "("+nData.nextToken() + ", "
                + colorGen.next() + ", '"
                + lData.nextToken() + "', green)";

System.out.println(SliceData);
if (nData.hasMoreTokens()) {SliceData += ", ";}
}

try {
                  // We already instantiated the pie chart
                  // class(NFPieChartAPP) at the top of the applet.
                pie.init();
                pie.start();
                  // Initialize and start the pie chart class.

                pie.loadParams(
                "Background=(black, RAISED, 4);"+
                "Header=('Cost Information (millions)');"+
                "LabelPos=0.7;"+
                "DwellLabel      = ('', black, 'TimesRoman', 16);"+
                "Legend        = ('Legend', black);"+
                "LegendBox     = (white, RAISED, 4);"+
                "Slices=(12.3, blue, 'Marketing', cyan), (4.6,
                antiquewhite, 'Sales'), (40.1, aqua, 'Production'),
                (18.4, aquamarine, 'Support');");

                  // Above, we set the parameters for the pie chart,
                  // including the data and  labels which we generated
                  // in the loop above ( SliceData ), and the Legend,
                  // label position, header, and other properties.
                  // Again, have a look at the NetCharts documentation
                  // for all of the possible parameters.

                pie.loadParams ("Update");
                  // Tell the pie chart class we've sent it new
                  // parameters to display.
        } catch (Exception e) {
                System.out.println (e.getMessage());
        }
}

// Below is the same as before except for the new ColorGenerator class
// that we needed to produce distinct colors.

public String[] getData( String QueryLine ) {

  int columns, pos;
  String column[]=new String[4];
  boolean more;

 try {

      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(QueryLine);
      columns=(rs.getMetaData()).getColumnCount();

      column = new String[columns];

      // Initialize the columns to be blank
      for(pos=1; pos<=columns; pos++) {
        column[pos-1]="";
      }

      more=rs.next();

      while(more) {for (pos=1; pos<=columns; pos++) {
          column[pos-1]+=(rs.getString(pos));
        }

        more=rs.next();
        for (pos=1; pos<=columns; pos++) {
          if(more) {
            column[pos-1]+=(",");
          }
        }
      }
      stmt.close();
      // con.close();
    }
    catch( Exception e ) {
      e.printStackTrace();
      System.out.println(e.getMessage());
    }

return column;
}
public void destroy() {

  try {con.close();}
  catch( Exception e ) {
      e.printStackTrace();
      System.out.println(e.getMessage());
    }
}
}


class ColorGenerator {
// This class is needed to produce colors that the pie chart can use to
// color the slices properly. They are taken from the NetCharts color
// class, NFColor.
public ColorGenerator() {

}

int color_count = -1;
// Keep a running count of the colors we have used. We'll simply index
// the colors in a String array, and call up the incremented counter to
// get a new color. If you need more colors than are added below, you can
// add more by pulling them from the NFColor class found in the NetCharts
// package on the CD-ROM or Web site.

String colors[] =
{"aliceblue","antiquewhite","aqua","aquamarine","azure","beige",
"bisque","black","blanchedalmond","blue","blueviolet","brown","chocolate",
"cadetblue","chartreuse","cornsilk","crimson","cyan"};

public String next() {

// Increment the color counter, and return a String which contains the
// color at this index.
  color_count += 1;
  return colors[color_count];

}

} // end example72.java

Summary

This chapter has shown you how to generate meaningful charts to represent data obtained from a query. We’ve seen how to create both bar and pie charts. You can use the properties of the NetCharts package to customize your charts as you wish, and there are many more options in the package that haven’t been shown in the examples here.

In the next chapter, we will continue to discuss working with database query results, and we will provide a complete code example for showing SQL BLOB data types. It shows you how to get an image from the ResultSet, as well as how to add images or binary data to a table in a database.


Previous Table of Contents Next